# 数据库连接模块
import pymysql
import user

def sqlConncet(database,account):
  '''
  database:数据库名称 | account:sql用户\n
  实例化一个数据库连接对象
  '''
  connect = pymysql.connect(
              host='localhost',
              port=3306,
              user=f'{account}',
              password=f'{user.account_dict[account]}',
              db=f'{database}',
              charset='utf8'
          )
  return connect

def sqlGetData(database,sql: str,user=user.default_user):
  '''
  database:数据库名称 | sql:sql语句 | user:sql用户\n
  读取sql表
  '''
  # 操作数据库
  try:
    connect = sqlConncet(database,user)
    cursour = connect.cursor()
    cursour.execute(sql)
    result = cursour.fetchall()
  except:
    result = "输入格式有误"
  connect.close()
  return result

def dataToSql(source,database,table,mode):
  '''
  mode:'append'(不改变表结构) 或 'replace'(表结构清空) | source:pandas的DataFrame | table:目标表的名称\n
  pandas表入库
  '''
  from sqlalchemy import create_engine
  engine = create_engine('mysql+pymysql://{}:{}@localhost:3306/{}?charset=utf8'.format(user.account,user.account_dict[user.account],database))
  # 数据导出到sql
  source.to_sql(name='{}'.format(table), con=engine, if_exists='{}'.format(mode),index=False)

def selectMultiData(database,sql_list: list,user=user.default_user):
  '''
  database:数据库名称 | sql_list:sql语句列表 | user:sql用户\n
  查询多表记录
  '''
  try:
    connect = sqlConncet(database,user)
    cursour = connect.cursor()
    for sql in sql_list:
      print(sql)
      cursour.execute(sql)
    result = cursour.fetchall()
  except:
    result = "select failed"
  connect.close()
  return result

def OperateDataOnce(database,sql: str,user=user.default_user):
  '''
  database:数据库名称 | sql:sql语句 | user:sql用户\n
  插入或更新一条sql表记录
  '''
  try:
    connect = sqlConncet(database,user)
    cursour = connect.cursor()
    cursour.execute(sql)
    connect.commit()
    operation_tip = 'operate OK'
  except:
    connect.rollback()
    operation_tip = 'operate error'
  connect.close()
  return operation_tip

def InsertData(database,sql: str,user=user.default_user):
  '''
  database:数据库名称 | sql:sql语句 | user:sql用户\n
  插入sql表记录
  '''
  try:
    connect = sqlConncet(database,user)
    cursour = connect.cursor()
    cursour.execute(sql)
    connect.commit()
    operation_tip = 'insert OK'
  except:
    connect.rollback()
    operation_tip = 'insert error'
  connect.close()
  return operation_tip

def UpdateData(database,sql: str,user=user.default_user):
  '''
  database:数据库名称 | sql:sql语句 | user:sql用户\n
  更新sql表记录
  '''
  try:
    connect = sqlConncet(database,user)
    cursour = connect.cursor()
    cursour.execute(sql)
    connect.commit()
    operation_tip = 'update OK'
  except:
    connect.rollback()
    operation_tip = 'update error'
  connect.close()
  return operation_tip

def InsertMultiData(database,sql_list: list,user=user.default_user):
  '''
  database:数据库名称 | sql_list:sql语句列表 | user:sql用户\n
  插入多条记录
  '''
  try:
    connect = sqlConncet(database,user)
    cursour = connect.cursor()
    for sql in sql_list:
      cursour.execute(sql)
    connect.commit()
    operation_tip = 'insert OK'
    connect.close()
    return operation_tip
  except:
    connect.rollback()
    operation_tip = 'insert error'
    connect.close()
  return operation_tip

def DeleteData(database,sql_list,user=user.default_user):
  '''
  database:数据库名称 | sql_list:sql语句列表 | user:sql用户\n
  删除多条记录
  '''
  try:
    connect = sqlConncet(database,user)
    cursour = connect.cursor()
    for sql in sql_list:
      cursour.execute(sql)
    connect.commit()
    operation_tip = 'insert OK'
    connect.close()
    return operation_tip
  except:
    connect.rollback()
    operation_tip = 'insert error'
  connect.close()
  return operation_tip